Hi everyone in this article, I’m explaining about offline crud operation using HTML5 and IndexedDB database

Description:

You can use HTML5 web storage, IndexedDB or File access API in your app to provide offline support. It allows user to work offline when the network isn’t available. An IndexedDB is a persistent data store in the browser means a database on the client side. It allows you to create app with rich query abilities in both offline and online mode. If you are beginner with IndexedDB, you should first read Using IndexedDB. 
In this tutorial, we will implement add, edit, delete operations on IndexedDB using Linq2IndexedDB library. Being a .NET guy, I am fan of LINQ, this library provides a way to use LINQ type syntax to perform operations on IndexedDB. It saves bunch of time and code. We are going to implement following thing to save customer data:

1.    Add jquery, jquery ui and modernizer references in the page.

2.   Download Linq2IndexedDB library and add indexeddb.shim.js and Linq2IndexedDB.js files

<linkrel="stylesheet"href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.9/themes/base/jquery-ui.css"type="text/css"media="all"/>
<scriptsrc="//ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<scriptsrc="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.9/jquery-ui.min.js"type="text/javascript"></script>
<scriptsrc="~/script/modernizr-2.6.2.js"></script>
<scriptsrc="~/script/indexeddb.shim.js"></script>
<scriptsrc="~/script/Linq2IndexedDB.js"></script>

HTML Code

<divstyle="width: 100%; text-align: center; clear: both; padding-top: 10px">
    <divid="contentHolder"style="margin: 0auto; width: 425px">
    </div>
    <buttonid="btnAddCustomer">
        Add Customer</button>
    <buttonid="btnDeleteDB">
        Clear Local DB</button>
    <divid="dialog-form"title="Add new customer">
        <form>
            <fieldset>
                <labelfor="Id"id="lblId"class="invisible">
                    ID</label>
                <inputtype="number"name="Id"id="txtId"value=""class="text ui-widget-content ui-corner-all invisible"/>
                <labelfor="Name"id="lblName">
                    Name</label>
                <inputtype="text"name="Name"id="txtName"class="text ui-widget-content ui-corner-all"/>
                <labelfor="Email"id="lblEmail">
                    Email</label>
                <inputtype="email"name="Email"id="txtEmail"value=""class="text ui-widget-content ui-corner-all"/>
                <labelfor="Phone"id="lblPhone">
                    Phone</label>
                <inputtype="tel"name="Phone"id="txtPhone"value=""class="text ui-widget-content ui-corner-all"/>
            </fieldset>
        </form>
    </div>
</div>

We’ll generate a grid dynamically in contentHolder. btnDeleteDB is used to clear

local IndexedDB.

Configure the Database:

The first logical step is to configure database and create instance of the

linq2indexeddb object:

var config = {
    dbName: 'CustomerDB',
    version: 1,
    objectStoreName: 'Customers',
    appID: 123
};
 
var databaseDefinition = [{
    version: config.version,
    objectStores: [{ name: config.objectStoreName, objectStoreOptions: { autoIncrement: false,keyPath: "Email" } }],
    indexes: [{ objectStoreName:config.objectStoreName, propertyName: "Email", indexOptions: { unique: true, multirow: false } }]
}];
 
var dbConfig = {
    version: config.version,
    definition: databaseDefinition
};
 
if (!localStorage.customerRevision) {
    localStorage.customerRevision = -1;
}
 
var db = window.linq2indexedDB(config.dbName, dbConfig, false);

Here we define database name CustomerDB and ObjectStore name Customers.

Check documentation for more about database configuration parameters.

Here localStorageis used to save current revision of database.

Preview:

Load Gridview:

/*Load Gridview*/
db.initialize().then(function () {
    InitializeData();
}, handleError);
 
function InitializeData() {
    var content = $('#contentHolder');
    var table = $('<table id="tblCustomer" class="ui-widget ui-widget-content"></table>');
    content.append(table);
    var thead = $('<thead></thead>');
    table.append(thead);
    var tr = $('<tr class="ui-widget-header"></tr>');
    thead.append(tr);
    tr.append('<th>Name</th><th>Email</th><th>Phone</th><th>Actions</th>');
    var tbody = $('<tbody id="customer-data"></tbody>');
    table.append(tbody);
 
    $('#customer-data').empty();
    db.linq.from(config.objectStoreName).where("IsDeleted").equals("0").orderBy("Email").select().then(function () {
    }, handleError, function (sdata) {
        showCustomer(sdata);
    });
 
}
 
function showCustomer(data) {
    var row = $('tr[value="' + data.Email + '"]');
    if (row.length > 0) {
        row.empty();
    } else {
        row = $('<tr value="' + data.Email + '">');
    }
    row.append('<td>' + data.Name + '</td><td>' + data.Email + '</td><td>' + data.Phone + '</td>');
    var upd = $('<button type="button" value="' + data.Email + '">Update</button>');
    upd.button().click(function () {
        getCustomer(this);
    });
 
    var del = $('<button type="button" value="' + data.Email + '">Delete</button>');
    del.button().click(function () {
        deleteCustomer(this);
    });
 
    var col = $('<td></td>');
    col.append(upd);
    col.append(del);
    row.append(col);
    $('#customer-data').append(row);
}
 
function handleError(error) {
    alert(error);
}

InitializeData: To draw table structure

showCustomer: to add customer details in rows with update and delete buttons.

Add Customer:

/*Add Customer*/
$('#btnAddCustomer').click(function () {
    $("#txtId").val(0);
    $("#txtName").val('');
    $("#txtEmail").val('');
    $("#txtEmail").removeAttr('disabled').css({ 'color': 'black' });;
    $("#txtPhone").val('');
    $("#dialog-form").dialog("open");
});
 
 
$("#dialog-form").dialog({
    autoOpen: false,
    height: 300,
    width: 350,
    modal: true,
    buttons: {
        "Save": function () {
            var bValid = true;
 
            if (bValid) {
                var id = parseInt($("#txtId").val());
                var customer = {};
                if (id != 0) {
                    customer.CustomerID = id;
                }
                customer.Name = $("#txtName").val();
                customer.Email = $("#txtEmail").val();
                customer.Phone = $("#txtPhone").val();
                customer.Revision = -1;
                customer.IsDeleted = 0;
                saveCustomer(customer);
                $(this).dialog("close");
            }
        },
        Cancel: function () {
            $(this).dialog("close");
        }
    },
    close: function () {
    }
});

When a new customer is added, It will have CustomerID = 0. the above dialog is used for both add and edit operations. when any change(add/edit) is made, the Revision property gets reset to -1. We’ll use these Revision and other properties in the next post to sync with online server database.

Preview:

Edit Customer:

We are using Email property as unique criteria, So user can’t modify Email during

editing.

/*Edit Customer*/
 
function getCustomer(btn) {
    db.linq.from(config.objectStoreName).get($(btn).val()).then(InitializeUpdate, handleError);
}
 
 
function InitializeUpdate(customer) {
    $("#txtId").val(customer.CustomerID);
    $("#txtName").val(customer.Name);
    $("#txtEmail").val(customer.Email);
    $("#txtPhone").val(customer.Phone);
    $("#txtEmail").attr('disabled', 'disabled').css({ 'color': 'gray' });
    $("#dialog-form").dialog("open");
}
 
 
function saveCustomer(customer) {
    var emails = [];
    //get all localDB emails
    db.linq.from(config.objectStoreName).select(["Email"]).then(function () {
        if ((customer.CustomerID && customer.CustomerID != 0) || $.inArray(customer.Email, emails) > -1) {
            db.linq.from(config.objectStoreName).update(customer).then(function (data) {
                showCustomer(data.object);
            }, handleError);
        } else {
            customer.CustomerID = -1;
            db.linq.from(config.objectStoreName).insert(customer).then(function (data) {
                showCustomer(data.object);
            }, handleError);
        }
    }, handleError, function (data) {
        emails.push(data.Email);
    });
}

When user clicks on update button, getCustomer method is called to get data andInitializeUpdate sets the data in dialog’s controls. On save click in the dialog,saveCustomer method (common in both add/ edit operations) is called. In online database, we are not deleting record, only setting IsDeleted flag. If user creates new customer with same deleted email then we have to reactivate old account and update information, that’s why, first all emails are fetched and if there is valid CustomerID OR Email already exists, update operation is performed in local database else Insert is peformed after setting CustomerID =-1 means all new inserted customers will have CustomerID -1 .

Delete Customer:

/* Delete Customer*/
function deleteCustomer(btn) {
 
    db.linq.from(config.objectStoreName).get($(btn).val()).then(function (data) {
 
        if (data.CustomerID == -1) {
            //Delete local record which is not saved on server yet
            db.linq.from(config.objectStoreName).remove(data.Email).then(function () {
                $(btn).parents('tr').remove();
            }, handleError);
        }
        else {
            data.IsDeleted = 1;
            data.Revision = -1;
            db.linq.from(config.objectStoreName).update(data).then(function (data) {
                $(btn).parents('tr').remove();
            }, handleError);
        }
    }, handleError);
}

We are deleting records which are created offline(CustomerID = -1) and never

deployed on the server. If any server side data is deleted, we are marking IsDeleted

= 1.

Clear Database:

//Reset Local IndexedDB
$('#btnDeleteDB').click(function () {
    db.deleteDatabase().then(function () {
        db.initialize().then(function () {
            $('#tblCustomer').remove();
            localStorage.customerRevision = -1;
            InitializeData();
        }, handleError);
    });
});

Output:

in my next post i'll discuss about Syncing Offline Database (HTML5 IndexedDB) with Online Database using Asp.Net Web API